# 导入必要的库
import pandas as pd  # 用于数据处理和分析
import openpyxl  # 用于操作Excel文件
from openpyxl import load_workbook  # 用于加载现有的Excel工作簿
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment  # 用于处理Excel单元格样式
import re  # 用于正则表达式操作

def copy_row_formatting(source_row, target_row, ws):
    """
    复制源行的格式到目标行

    功能：将源行的所有格式属性（字体、填充、边框、对齐、数字格式、公式）复制到目标行
    参数:
        source_row: int - 源行号，要复制格式的行
        target_row: int - 目标行号，要应用格式的行
        ws: Worksheet - openpyxl工作表对象
    返回值: 无
    """
    # 遍历源行的所有单元格，从第1列到最大列
    for col_idx in range(1, ws.max_column + 1):
        # 获取源行指定列的单元格对象
        source_cell = ws.cell(row=source_row, column=col_idx)
        # 获取目标行指定列的单元格对象
        target_cell = ws.cell(row=target_row, column=col_idx)

        # 复制字体格式 - 检查源单元格是否有字体设置
        if source_cell.font:
            # 创建新的字体对象，复制源单元格的所有字体属性
            target_cell.font = Font(
                name=source_cell.font.name,  # 字体名称（如：Arial、宋体）
                size=source_cell.font.size,  # 字体大小（如：11、12）
                bold=source_cell.font.bold,  # 是否加粗（True/False）
                italic=source_cell.font.italic,  # 是否斜体（True/False）
                color=source_cell.font.color  # 字体颜色（RGB值）
            )

        # 复制填充格式（背景色）- 检查源单元格是否有填充设置
        if source_cell.fill:
            # 创建新的填充对象，复制源单元格的所有填充属性
            target_cell.fill = PatternFill(
                fill_type=source_cell.fill.fill_type,  # 填充类型（如：solid实心填充）
                start_color=source_cell.fill.start_color,  # 起始颜色（RGB值）
                end_color=source_cell.fill.end_color  # 结束颜色（RGB值）
            )

        # 复制边框格式 - 检查源单元格是否有边框设置
        if source_cell.border:
            # 创建新的边框对象，复制源单元格的所有边框属性
            target_cell.border = Border(
                left=source_cell.border.left,  # 左边框样式
                right=source_cell.border.right,  # 右边框样式
                top=source_cell.border.top,  # 上边框样式
                bottom=source_cell.border.bottom  # 下边框样式
            )

        # 复制对齐方式 - 检查源单元格是否有对齐设置
        if source_cell.alignment:
            # 创建新的对齐对象，复制源单元格的所有对齐属性
            target_cell.alignment = Alignment(
                horizontal=source_cell.alignment.horizontal,  # 水平对齐（left/center/right）
                vertical=source_cell.alignment.vertical,  # 垂直对齐（top/center/bottom）
                wrap_text=source_cell.alignment.wrap_text,  # 是否自动换行（True/False）
                shrink_to_fit=source_cell.alignment.shrink_to_fit  # 是否缩小字体填充（True/False）
            )

        # 复制数字格式 - 直接复制，不需要检查（如：日期、货币、百分比等格式）
        target_cell.number_format = source_cell.number_format

        # 复制公式 - 检查源单元格是否包含公式
        # 'f' 表示公式类型（formula），如果源单元格是公式类型
        if source_cell.data_type == 'f':
            # 调整公式中的单元格引用，使其适应新的行位置
            # 例如：将A1引用调整为A2，当从第1行复制到第2行时
            adjusted_formula = adjust_formula_references(source_cell.value, source_row, target_row)
            # 将调整后的公式设置到目标单元格
            target_cell.value = adjusted_formula

    # 复制行高 - 在单元格循环之外处理
    # 设置目标行的行高与源行相同
    ws.row_dimensions[target_row].height = ws.row_dimensions[source_row].height


def adjust_formula_references(formula, source_row, target_row):
    """
    调整公式中的单元格引用，使其适应新的行位置

    功能：将公式中的相对引用单元格地址根据行号差异进行调整
    参数:
        formula: str - 原始公式字符串
        source_row: int - 源行号
        target_row: int - 目标行号
    返回值: str - 调整后的公式字符串
    """
    # 检查公式是否有效：如果为空或不以等号开头，直接返回原公式
    if not formula or not formula.startswith('='):
        return formula

    # 正则表达式模式：匹配Excel单元格引用
    # 匹配模式解释：
    # \$? - 可选的美元符号（表示绝对引用）
    # [A-Z]{1,3} - 1到3个大写字母（Excel列标识，最大到ZZZ）
    # \$? - 可选的美元符号（表示绝对引用）
    # [0-9]{1,7} - 1到7个数字（Excel行号，最大9999999）
    cell_ref_pattern = r'(\$?[A-Z]{1,3}\$?[0-9]{1,7})'

    def adjust_match(match):
        """
        内部函数：处理单个单元格引用的调整

        参数:
            match: re.Match - 正则表达式匹配对象
        返回值: str - 调整后的单元格引用
        """
        # 获取匹配的完整单元格引用字符串（如：A1、$B$2、C$3等）
        cell_ref = match.group(1)

        # 使用正则表达式提取列部分（字母部分）
        # 匹配列标识：可选的$符号 + 1-3个大写字母
        col_part = re.search(r'(\$?[A-Z]{1,3})', cell_ref).group(1)
        # 使用正则表达式提取行部分（数字部分）
        row_part = re.search(r'(\$?[0-9]{1,7})', cell_ref)

        # 如果找到了行部分（数字部分）
        if row_part:
            row_str = row_part.group(1)  # 获取行号字符串
            # 检查是否是绝对引用（行号前有$符号）
            if row_str.startswith('$'):
                # 如果是绝对引用，保持原样不调整
                return cell_ref
            else:
                # 如果是相对引用，需要调整行号
                row_num = int(row_str)  # 将行号字符串转换为整数
                row_diff = target_row - source_row  # 计算源行和目标行之间的差值
                new_row = row_num + row_diff  # 应用行号偏移量
                # 返回调整后的单元格引用（列部分保持不变，行号更新）
                return f"{col_part}{new_row}"
        else:
            # 如果没有找到行部分，保持原单元格引用不变
            return cell_ref

    # 使用正则表达式替换所有匹配的单元格引用
    # 对公式中每个匹配的单元格引用调用adjust_match函数进行调整
    adjusted_formula = re.sub(cell_ref_pattern, adjust_match, formula)
    # 返回调整后的完整公式
    return adjusted_formula


def find_all_formula_cells(ws):
    """
    查找工作表中所有包含公式的单元格

    参数:
        ws: Worksheet - openpyxl工作表对象
    返回值: dict - 字典，键为单元格坐标（如"A1"），值为公式内容
    """
    formula_cells = {}

    # 遍历工作表的每一行，从第1行到最后一行
    for row in range(1, ws.max_row + 1):
        # 遍历工作表的每一列，从第1列到最后一列
        for col in range(1, ws.max_column + 1):
            # 获取当前行列位置的单元格对象
            cell = ws.cell(row=row, column=col)

            # 检查单元格是否包含公式
            # cell.data_type == 'f' 表示单元格类型为公式
            if cell.data_type == 'f' and cell.value:
                # 将单元格坐标和公式内容存入字典
                formula_cells[f"{cell.column_letter}{cell.row}"] = cell.value

    return formula_cells


def update_all_formulas(ws, inserted_rows, start_row):
    """
    更新工作表中所有受影响的公式，使其适应新插入的行

    功能：调整所有公式中的单元格引用，特别是那些引用插入行以下区域的公式
    参数:
        ws: Worksheet - openpyxl工作表对象
        inserted_rows: int - 插入的行数
        start_row: int - 插入行的起始行号
    返回值: 无
    """
    # 查找工作表中所有包含公式的单元格
    formula_cells = find_all_formula_cells(ws)

    # 遍历每个包含公式的单元格
    for cell_coord, formula in formula_cells.items():
        cell = ws[cell_coord]  # 通过单元格坐标获取单元格对象

        # 获取当前单元格的行号
        current_row = cell.row

        # 如果当前单元格在插入区域下方，需要调整其行号
        if current_row > start_row + inserted_rows:
            # 计算新的行号
            new_row = current_row + inserted_rows
            # 调整公式中的引用
            new_formula = adjust_formula_references(formula, current_row, new_row)
            # 更新单元格公式
            cell.value = new_formula


def find_sum_formula_cells(ws):
    """
    查找工作表中所有包含SUM公式的单元格

    功能：遍历工作表的每个单元格，找到所有包含SUM函数的公式单元格
    参数:
        ws: Worksheet - openpyxl工作表对象
    返回值: dict - 字典，键为单元格坐标（如"A1"），值为公式内容
    """
    # 初始化空字典，用于存储找到的SUM公式单元格
    sum_cells = {}

    # 遍历工作表的每一行，从第1行到最后一行
    for row in range(1, ws.max_row + 1):
        # 遍历工作表的每一列，从第1列到最后一列
        for col in range(1, ws.max_column + 1):
            # 获取当前行列位置的单元格对象
            cell = ws.cell(row=row, column=col)

            # 检查单元格是否包含公式且公式中包含SUM函数
            # cell.data_type == 'f' 表示单元格类型为公式
            # cell.value 检查单元格值不为空
            # 'SUM(' in cell.value.upper() 检查公式中是否包含SUM函数（不区分大小写）
            if cell.data_type == 'f' and cell.value and 'SUM(' in cell.value.upper():
                # 将单元格坐标和公式内容存入字典
                # cell.column_letter 获取列字母（如A、B、C）
                # cell.row 获取行号
                sum_cells[f"{cell.column_letter}{cell.row}"] = cell.value

    # 返回包含所有SUM公式单元格的字典
    return sum_cells


def update_sum_formulas(ws, inserted_rows):
    """
    更新工作表中的SUM公式，使其包含新插入的行

    功能：调整SUM公式中的范围引用，使其包含新插入的行
    参数:
        ws: Worksheet - openpyxl工作表对象
        inserted_rows: int - 插入的行数
    返回值: 无
    """
    # 查找工作表中所有包含SUM公式的单元格
    sum_cells = find_sum_formula_cells(ws)

    # 遍历每个包含SUM公式的单元格
    for cell_coord, formula in sum_cells.items():
        # 通过单元格坐标获取单元格对象
        cell = ws[cell_coord]

        # 使用正则表达式匹配SUM函数的参数部分
        # 模式解释：SUM\( 匹配"SUM("，([^)]+) 匹配一个或多个非右括号的字符，\) 匹配右括号
        sum_pattern = r'SUM\(([^)]+)\)'
        # 查找所有匹配的SUM函数参数
        matches = re.findall(sum_pattern, formula, re.IGNORECASE)

        # 初始化新公式为原始公式（如果没有匹配到，则保持原样）
        new_formula = formula
        # 遍历每个匹配到的SUM函数参数
        for match in matches:
            # 分割多个范围参数（SUM函数可以有多个用逗号分隔的范围）
            ranges = match.split(',')
            # ranges = match    #由于底稿中sum函数通常不涉及逗号分割，更改ranges
            # 创建空列表存储调整后的范围
            new_ranges = []

            # 遍历每个范围字符串
            for range_str in ranges:
                # 检查是否是单元格范围（包含冒号分隔符）
                if ':' in range_str:
                    # 分割起始单元格和结束单元格
                    start_cell, end_cell = range_str.split(':')

                    # 提取开始单元格的列部分（字母）
                    start_col_part = re.search(r'([A-Z]+)', start_cell).group(1)
                    # 提取开始单元格的行部分（数字）
                    start_row_part = re.search(r'([0-9]+)', start_cell)

                    # 如果成功提取到行号
                    if start_row_part:
                        # 将行号字符串转换为整数
                        start_row = int(start_row_part.group(1))
                        # 扩展开始行，使其包含所有新插入的行；由于之前遍历所有公式（包括sum公式）现在需将开始单元格行号减少插入行号
                        new_start_row = start_row - inserted_rows
                        # 构建新的开始单元格地址
                        new_start_cell = f"{start_col_part}{new_start_row}"
                        # 构建新的范围字符串
                        new_range = f"{start_cell}:{new_start_cell}"
                        # 将新范围添加到列表中
                        new_ranges.append(new_range)
                    else:
                        # 如果没有找到行号，保持原范围不变
                        new_ranges.append(range_str)
                else:
                    # 如果不是范围（可能是单个单元格或函数），保持原样
                    new_ranges.append(range_str)

            # 构建新的SUM函数参数（用逗号连接所有范围）
            new_sum_param = ','.join(new_ranges)
            # 使用新的参数替换原始公式中的SUM函数
            new_formula = re.sub(
                sum_pattern,  # 要替换的模式
                f'SUM({new_sum_param})',  # 替换内容
                new_formula,  # 原始公式
                flags=re.IGNORECASE  # 忽略大小写
            )

        # 将更新后的公式设置回单元格
        cell.value = new_formula


def parse_sumif_formula(formula):
    """
    解析SUMIF公式，提取其参数

    参数:
    - formula: 字符串，包含SUMIF公式的文本，例如'=SUMIF(Sheet2!A1:A10,"苹果",Sheet2!B1:B10)'

    返回值:
    - 元组 (range_part, criteria, sum_range):
        - range_part: 字符串，条件范围，例如"Sheet2!A1:A10"
        - criteria: 字符串，条件，例如'"苹果"'
        - sum_range: 字符串或None，求和范围，例如"Sheet2!B1:B10"，如果不存在则为None
    """

    # 定义正则表达式模式，用于匹配SUMIF公式的结构
    pattern = r'=SUMIF\((.*?),(.*?)(?:,(.*?))?\)'

    # 使用正则表达式匹配公式
    match = re.match(pattern, formula)

    # 检查是否匹配成功
    if match:
        # 提取第一个捕获组（条件范围）并去除前后空格
        range_part = match.group(1).strip()

        # 提取第二个捕获组（条件）并去除前后空格
        criteria = match.group(2).strip()

        # 提取第三个捕获组（求和范围，如果存在）
        sum_range = match.group(3).strip() if match.group(3) else None

        # 返回解析出的三个部分
        return range_part, criteria, sum_range

    # 如果正则表达式匹配失败，返回三个None值
    return None, None, None


def adjust_range_with_offset(range_str, row_offset):
    """
    调整范围字符串，增加行偏移量

    参数:
    - range_str: 字符串，原始范围，例如"Sheet2!$A$1:$A$10"
    - row_offset: 整数，要增加的行数

    返回值:
    - 调整后的范围字符串
    """
    # 检查是否包含工作表引用
    if '!' in range_str:
        # 分离工作表名称和单元格范围
        sheet_name, cell_range = range_str.split('!', 1)

        # 调整单元格范围
        adjusted_cell_range = adjust_cell_range_with_offset(cell_range, row_offset)

        return f"{sheet_name}!{adjusted_cell_range}"
    else:
        # 如果没有工作表引用，直接调整单元格范围
        return adjust_cell_range_with_offset(range_str, row_offset)


def adjust_cell_range_with_offset(cell_range, row_offset):
    """
    调整单元格范围，增加行偏移量

    参数:
    - cell_range: 字符串，单元格范围，例如"$A$1:$A$10"
    - row_offset: 整数，要增加的行数

    返回值:
    - 调整后的单元格范围
    """
    # 使用正则表达式匹配范围中的单元格引用
    # 匹配模式：$字母$数字 或 字母$数字
    pattern = r'(\$?[A-Za-z]+\$?\d+)'
    matches = re.findall(pattern, cell_range)

    if len(matches) == 2:  # 正常范围，如A1:A10
        start_cell, end_cell = matches

        # 调整起始单元格
        start_adjusted = adjust_cell_with_offset(start_cell, 0)

        # 调整结束单元格
        end_adjusted = adjust_cell_with_offset(end_cell, row_offset)

        return f"{start_adjusted}:{end_adjusted}"
    else:
        # 如果无法解析，返回原始范围
        return cell_range


def adjust_cell_with_offset(cell_ref, row_offset):
    """
    调整单元格引用，增加行偏移量

    参数:
    - cell_ref: 字符串，单元格引用，例如"$A$1"
    - row_offset: 整数，要增加的行数

    返回值:
    - 调整后的单元格引用
    """
    # 使用正则表达式分离列和行部分
    pattern = r'(\$?[A-Za-z]+)(\$?\d+)'
    match = re.match(pattern, cell_ref)

    if match:
        col_part = match.group(1)  # 列部分，如"$A"或"A"
        row_part = match.group(2)  # 行部分，如"$1"或"1"

        # 提取行号（去掉$符号）
        row_num = int(row_part.replace('$', ''))

        # 计算新行号
        new_row_num = row_num + row_offset

        # 重建单元格引用，保持原有的绝对引用格式
        if '$' in row_part:
            # 如果原来是绝对引用，保持绝对引用
            new_row_part = f"${new_row_num}"
        else:
            # 如果原来是相对引用，保持相对引用
            new_row_part = f"{new_row_num}"

        # 返回调整后的单元格引用
        return f"{col_part}{new_row_part}"
    else:
        # 如果无法解析，返回原始单元格引用
        return cell_ref


def update_all_sumif_ranges(sheet, sumif_range_update, sumif_sum_range_update):
    """
    更新工作表中所有SUMIF公式的条件范围和求和范围

    参数:
    - sheet: 工作表对象，通过openpyxl加载的工作表
    - sumif_range_update: 整数，条件范围要增加的行数
    - sumif_sum_range_update: 整数，求和范围要增加的行数
    """

    # 计数器，用于统计更新的公式数量
    updated_count = 0

    # 遍历工作表中的所有行
    for row in sheet.iter_rows():
        # 遍历当前行中的每个单元格
        for cell in row:
            # 检查单元格是否包含公式且公式中包含"SUMIF"
            if cell.data_type == 'f' and 'SUMIF' in cell.value:
                try:
                    # 调用parse_sumif_formula函数解析当前公式
                    range_part, criteria, sum_range = parse_sumif_formula(cell.value)

                    # 检查解析是否成功（range_part不为None）
                    if range_part is not None:
                        # 调整条件范围
                        adjusted_range = adjust_range_with_offset(range_part, sumif_range_update)

                        # 调整求和范围（如果存在）
                        adjusted_sum_range = None
                        if sum_range:
                            adjusted_sum_range = adjust_range_with_offset(sum_range, sumif_sum_range_update)

                        # 根据是否包含求和范围，重建SUMIF公式
                        if adjusted_sum_range:
                            new_formula = f'=SUMIF({adjusted_range}, {criteria}, {adjusted_sum_range})'
                        else:
                            new_formula = f'=SUMIF({adjusted_range}, {criteria})'

                        # 将新公式赋值给单元格
                        cell.value = new_formula

                        # 增加计数器
                        updated_count += 1

                    else:
                        # 如果解析失败，打印警告信息
                        print(f"警告: 无法解析单元格 {cell.coordinate} 中的SUMIF公式: {cell.value}")
                except Exception as e:
                    # 处理可能出现的异常
                    print(f"错误: 处理单元格 {cell.coordinate} 时发生异常: {str(e)}")

    # 打印批量更新总结
    print(f"SUMIF公式批量更新完成，共更新了 {updated_count} 个公式")


def adjust_print_settings(ws, original_max_row, inserted_rows):
    """
    调整工作表的打印设置以适应新增的行

    功能详解：
    1. 调整打印区域，确保包含所有新增的行
    2. 设置打印标题，确保每页都显示表头
    3. 调整页面设置，包括缩放比例和适合页面选项

    参数:
        ws: Worksheet - openpyxl工作表对象，需要调整打印设置的工作表
        original_max_row: int - 调整前的最大行号，用于参考
        inserted_rows: int - 插入的行数，用于计算新的打印范围

    返回值: 无
    """

    # 使用try-except块捕获可能的异常，确保即使打印设置失败也不影响主要功能
    try:
        # 调整打印区域，已知工作表已设置打印区域
        # 调用自定义函数调整打印区域
        adjust_print_area(ws, inserted_rows)

    # 捕获所有异常，确保程序不会因打印设置问题而崩溃
    except Exception as e:
        # 打印警告信息，但不中断程序执行
        print(f"调整打印设置时出现警告: {e}")


def adjust_print_area(ws, inserted_rows):
    """
    调整打印区域以包含新增的行

    功能详解：
    解析现有的打印区域设置，将结束行号增加插入的行数，
    确保打印区域包含所有新增的数据行

    参数:
        ws: Worksheet - openpyxl工作表对象
        inserted_rows: int - 插入的行数

    返回值: 无
    """

    # 获取当前打印区域设置
    # ws.print_area 返回字符串，如 'A1:G50' 或 'A1:G50,H1:J50'
    current_print_area = ws.print_area

    # 检查是否存在打印区域设置
    if current_print_area:
        # 将打印区域字符串按逗号分割成多个区域
        # 有些工作表的打印区域可能包含多个不连续的区域
        areas = current_print_area

        # 创建空列表存储调整后的区域
        new_areas = []

        # 遍历每个打印区域
        for area in areas:
            # 检查区域是否包含冒号（表示是一个范围，如A1:G50）
            if ':' in area:
                # 分割起始单元格和结束单元格
                # 例如：将'A1:G50'分割为'start_cell='A1''和'end_cell='G50''
                start_cell, end_cell = area.split(':')

                # 使用正则表达式提取结束单元格的行号
                # r'(\d+)$' 匹配字符串末尾的数字
                end_row_match = re.search(r'(\d+)$', end_cell)

                # 如果成功匹配到行号
                if end_row_match:
                    # 将匹配到的行号字符串转换为整数
                    end_row_num = int(end_row_match.group(1))

                    # 计算新的结束行号：原结束行号 + 插入的行数
                    new_end_row = end_row_num + inserted_rows

                    # 使用正则表达式替换结束单元格中的行号
                    # r'\d+$' 匹配字符串末尾的数字，用新的行号替换
                    new_end_cell = re.sub(r'\d+$', str(new_end_row), end_cell)

                    # 构建新的区域字符串
                    new_area = f"{start_cell}:{new_end_cell}"

                    # 将新区域添加到列表中
                    new_areas.append(new_area)
                else:
                    # 如果没有匹配到行号，保持原区域不变
                    new_areas.append(area)
            else:
                # 如果不是范围（可能是单个单元格），保持原样
                new_areas.append(area)

        # 将调整后的区域列表用逗号连接成字符串，设置回工作表的打印区域
        ws.print_area = ','.join(new_areas)